<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2017/4/19
 * Time: 14:57
 */

namespace Admin\Controller;


use Think\Controller;

class MlController extends AdminController
{
    //对父类构造函数的补充
    public function _myinitialize()
    {
        $this->db = M('khzy');
        $this->db1 = M('dkzy');
        $this->db2 = M('cszy');
        $this->db3 = M('fkcb');
        $this->db4=M('ftth');
    }
        //www.glxt.com/admin/Ml/lst
    public function lst()
    {

        $where['status'] = array('gt', 0);

        //所有客户名称列表
        $khmc_arr = $this->db->where($where)->getField('khmc', true);
        $khmc_arr = array_unique($khmc_arr);
        $this->assign('khmc_arr', $khmc_arr);

        //所有运营商类型列表
        $yyslx_arr = $this->db->where($where)->getField('yyslx', true);
        $yyslx_arr = array_unique($yyslx_arr);
        $this->assign('yyslx_arr', $yyslx_arr);

        //所有业务区域列表
        $ywqy_arr = $this->db->where($where)->getField('ywqy', true);
        $ywqy_arr = array_unique($ywqy_arr);
        $this->assign('ywqy_arr', $ywqy_arr);

        //所有业务版块列表
        $ywbk_arr = $this->db->where($where)->getField('ywbk', true);
        $ywbk_arr = array_unique($ywbk_arr);
        $this->assign('ywbk_arr', $ywbk_arr);

        $month = I('month');
        $year = I('year');
        if (!empty($month) && !empty($year)) { //所属月份
            $ssyf = $year . $month;
            $where['yf'] =$ssyf;
        }

        $khmc = I('khmc', '', 'trim');
        $yyslx = I('yyslx', '', 'trim');
        $ywqy = I('ywqy', '', 'trim');
        $ywbk = I('ywbk', '', 'trim');

        if (!empty($khmc)) $where['khmc'] = $khmc;
        if (!empty($yyslx)) $where['yyslx'] = $yyslx;
        if (!empty($ywqy)) $where['ywqy'] = $ywqy;
        if (!empty($ywbk)) $where['ywbk'] = $ywbk;

        if (count($where) === 1) { //此时查询条件仅为status=1，表示没有别的查询条件，则默认查询条件为：只显示上月的数据
            $last_m = date("Ym",strtotime(date("Ym01",time()))-8600);;//默认情况下显示上月的数据
            $where['yf'] = $last_m;
        }

        //先根据条件查出所有的客户信息
        $khzy_arr = $this->db->where($where)->select();

        $lsts_arr = array();
        //按客户分组
        $khmc_arr = array_unique(array_column($khzy_arr, 'khmc'));
        foreach ($khzy_arr as $k => $v) {
            foreach ($khmc_arr as $v1) {
                if ($v['khmc'] === $v1) {
                    $lsts_arr[$v1][] = $v;
                }
            }
        }
        unset($k, $v, $v1);
        //求出khzy中带宽数量(gs)和业务收入(ys)之和
        $lsts = array();
        foreach ($lsts_arr as $k => &$v) {
            $gs_sum = array_sum(array_column($v, 'gs'));
            $ys_sum = array_sum(array_column($v, 'ys'));
            $lsts[$k]['sssj'] = $v[0]['sssj'];//所属时间
            $lsts[$k]['ywqy'] = $v[0]['ywqy'];//业务区域
            $lsts[$k]['ywbk'] = $v[0]['ywbk'];//业务板块
            $lsts[$k]['yyslx'] = $v[0]['yyslx'];//运营商类型
            $lsts[$k]['khmc'] = $k;//客户名称
            $lsts[$k]['dksl'] = $gs_sum;//带宽数量
            $lsts[$k]['ywsr'] = $ys_sum;//业务收入
            $lsts[$k]['dkzy_ids'] = $v[0]['dkzy_ids'];
            $lsts[$k]['cszy_ids'] = $v[0]['cszy_ids'];
        }
        unset($k, $v, $lsts_arr);
        foreach ($lsts as $k => $v) {
            $dkzy_ids = $v['dkzy_ids'];
            $cszy_ids = $v['cszy_ids'];

            //带宽成本
            if(!empty($dkzy_ids)){
                $condition['id'] = array('in', $dkzy_ids);
                $cjcb_arr = $this->db1->where($condition)->getField('sjcb', true);
            }else{
                $cjcb_arr=array();
            }

            $lsts[$k]['dkcb'] = array_sum($cjcb_arr);

            //传输成本
            if(!empty($cszy_ids)){
                $map['id'] = array('in', $cszy_ids);
                $cs_ys_arr = $this->db2->where($map)->getField('ys', true);
            }else{
                $cs_ys_arr=array();
            }

            $lsts[$k]['cscb'] = array_sum($cs_ys_arr);

            //返款成本
            unset($where['khmc'], $where['yyslx'], $where['ywqy'], $where['ywbk']);
            $where['dykh'] = $k;
            $fk_ys_arr = $this->db3->where($where)->getField('ys', true);
            $lsts[$k]['fkcb'] = array_sum($fk_ys_arr);

            //ftth成本
            unset($where['dykh']);
            $where['khmc']=$k;
            $ftth_sy=$this->db->where($where)->getField('ftth_sy');//求出对应客户当月的ftth使用百分比
            unset($where['khmc']);
            $ftth_xj_arr=$this->db4->where($where)->getField('xj',true);
            $ftthcb=array_sum($ftth_xj_arr);
            $lsts[$k]['ftthcb']=$ftthcb*$ftth_sy/100;

            //毛利额=业务收入-带宽成本-传输成本-返款成本
            $lsts[$k]['mle'] = $lsts[$k]['ywsr'] - $lsts[$k]['dkcb'] - $lsts[$k]['cszy'] - $lsts[$k]['fkcb']-$lsts[$k]['ftthcb'];
            $lsts[$k]['mll'] = round($lsts[$k]['mle'] / $lsts[$k]['ywsr'] * 100, 2) . "%";//毛利率=本月毛利额/业务收入*100%
        }
        $this->assign('lsts', $lsts);
        $this->display();

    }

}